package com.sx.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.dbutils.handlers.MapListHandler;

import com.annotation.BaseService;

import net.sf.ehcache.search.expression.And;

public class LifeIndexAirService {
	
	/**
	 * lifeList
	 * @param request
	 * @return
	 * @throws SQLException
	 */
	public  List<Object>  lifeList(HttpServletRequest request) throws SQLException {
		StringBuilder sb=new StringBuilder();
		String county = request.getParameter("county");
		sb.append("select city,county,updatetime,sunrise_1,sunset_1,")
		.append("l_detail_cl,l_name_cl,l_value_cl,")
		.append("l_detail_cy,l_name_cy,l_value_cy,")
		.append("l_detail_gm,l_name_gm,l_value_gm,")
		.append("l_detail_ls,l_name_ls,l_value_ls,")
		.append("l_detail_ly,l_name_ly,l_value_ly,")
		.append("l_detail_ssd,l_name_ssd,l_value_ssd,")
		.append("l_detail_xc,l_name_xc,l_value_xc,")
		.append("l_detail_yd,l_name_yd,l_value_yd,")
		.append("l_detail_yh,l_name_yh,l_value_yh,")
		.append("l_detail_ys,l_name_ys,l_value_ys,")
		.append("l_detail_zwx,l_name_zwx,l_value_zwx")
		.append(" from d_weather_real_forcast_life_warning where county='"+county+"'");
		List<Object> list = new ArrayList<>();
    	list.add(BaseService.queryList(sb.toString(), null));
		return list;
	} 
	/**
	 * airList
	 * 本市aqi 12小时变化
	 * @param request
	 * @return
	 * @throws SQLException
	 */
	public  List<Object>  airTrendList(HttpServletRequest request) throws SQLException {
//		select area,aqi,time_point from d_airqulity_allcity where area='宝鸡' ORDER BY time_point desc limit 0,24 ;
		String city = request.getParameter("city");
		String sql="select area,aqi,time_point from d_airqulity_allcity where area='"+city+"' ORDER BY time_point desc limit 0,12 ;";
		List<Object> list = new ArrayList<>();
    	list.add(BaseService.queryList(sql, null));
		return list;
	}
	/**
	 * 全省的排名和aqi指数
	 * @param request
	 * @return
	 * @throws SQLException
	 */
	public  List<Object>  orderList(HttpServletRequest request) throws SQLException {
		    String province=request.getParameter("pro");
			String sql="select d.area,d.aqi,d.time_point,d.order_air from b_weaher_china_city as b ,d_airqulity_allcity as d where b.city_name=d.area and b.province_name='"+province+"' and b.county_name = b.city_name and d.time_point=(SELECT max(time_point) from  d_airqulity_allcity) ORDER BY d.order_air;"  ;
			List<Object> list = new ArrayList<>();
	    	list.add(BaseService.queryList(sql, null));
			return list;
		} 
        
	
	/**
	 * airDetails
	 * @param request
	 * @return
	 * @throws SQLException
	 */
		public  List<Object>  airDetails(HttpServletRequest request) throws SQLException {
		String  city=request.getParameter("city");
		String sql="select * from d_airqulity_allcity as a ,d_airqulity_info as b where  a.`level`=b.AQI_LEVEL and area='"+city+"' and a.time_point =(select MAX(time_point) from d_airqulity_allcity);";
		System.out.println(sql);
		List<Object> list = new ArrayList<>();
    	list.add(BaseService.queryList(sql, null));
		return list;
	}
		
		
		
/**
 * <___________________________各监测点数据_________________________________>	
 */
		
		
		/**
		 * airList
		 * 本市各检测点 aqi 12小时变化
		 * @param request
		 * @return
		 * @throws SQLException
		 */
		public  List<Object>  cairTrendList(HttpServletRequest request) throws SQLException {
//			select area,aqi,time_point from d_airqulity_allcity where area='宝鸡' ORDER BY time_point desc limit 0,24 ;
			String city = request.getParameter("city");
			String position = request.getParameter("position");
			String sql="select area,aqi,time_point from d_airqulity_cnty where area='"+city+"' and position_name='"+position+"' ORDER BY time_point desc limit 0,12 ;";
			List<Object> list = new ArrayList<>();
	    	list.add(BaseService.queryList(sql, null));
			return list;
		}
		/**
		 * 全市的监测点排名和aqi指数
		 * @param request
		 * @return
		 * @throws SQLException
		 */
		public  List<Object>  corderList(HttpServletRequest request) throws SQLException {
			    String city = request.getParameter("city");
				String sql="select position_name,aqi,time_point from d_airqulity_cnty where area ='"+city+"' and time_point=(select max(t.time_point) from d_airqulity_cnty  as t)  and position_name is not null ORDER BY aqi asc;"  ;
				List<Object> list = new ArrayList<>();
		    	list.add(BaseService.queryList(sql, null));
				return list;
			} 
	        
		
		/**
		 * 
		 * 监测点实况
		 * airDetails
		 * @param request
		 * @return
		 * @throws SQLException
		 */
			public  List<Object>  cairDetails(HttpServletRequest request) throws SQLException {
			String  city=request.getParameter("city");
			String  position=request.getParameter("position");
			String sql="select * from d_airqulity_cnty as a ,d_airqulity_info as b where  a.`level`=b.AQI_LEVEL and area='"+city+"' and position = '"+position+"' and a.time_point =(select MAX(time_point) from d_airqulity_cnty);";
			List<Object> list = new ArrayList<>();
	    	list.add(BaseService.queryList(sql, null));
			return list;
		}
}
